﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Frunit.Model;
using OMG.Fill.Frunit.Model.Ent;
using OMG.Fill.Frunit.Service;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace OMG.Fill.Frunit.Services
{
    /// <summary>
    /// 技术推广人员情况表T_FruTecPromotion通用操作类
    /// </summary>
    public class FruTecPromotionServices : FruTecPromotions
    {
        public Pager<List<FruTecPromotionEntity>> GetPageList(FruTecPromotionQuery query)
        {
            Pager<List<FruTecPromotionEntity>> obj = new Pager<List<FruTecPromotionEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_FruTecPromotion where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<FruTecPromotionEntity>(strSql, paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal, paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取技术推广人员情况表T_FruTecPromotion分页列表失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 读取导入的Excel数据
        /// </summary>
        /// <param name="pathName">导入文件路径</param>
        /// <param name="fileSuffix">文件后缀</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="startRow">从第几行开始读取数据</param>
        /// <returns></returns>
        public List<FruTecPromotionModel> ExcelToModel(string pathName, string fileSuffix, string sheetName, int startRow)
        {

            IWorkbook workbook = null;
            ISheet sheet = null;
            List<FruTecPromotionModel> list = new List<FruTecPromotionModel>();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(4);//从第五行开始读
                int cellCount = firstRow.LastCellNum;//获取列数

                //获取有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                for (int i = startRow; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    FruTecPromotionModel model = new FruTecPromotionModel();
                    model.year = row.GetCell(0).ToString();
                    if (row.GetCell(1) != null && row.GetCell(1).ToString() != "")
                    {
                        model.InstituNum = int.Parse(row.GetCell(1).ToString());
                    }
                    else
                    {
                        model.InstituNum = null;
                    }
                    if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                    {
                        model.InstituNum_City = int.Parse(row.GetCell(2).ToString());
                    }
                    else
                    {
                        model.InstituNum_City = null;
                    }
                    if (row.GetCell(3) != null && row.GetCell(3).ToString() != "")
                    {
                        model.InstituNum_Area = int.Parse(row.GetCell(3).ToString());
                    }
                    else
                    {
                        model.InstituNum_Area = null;
                    }
                    if (row.GetCell(4) != null && row.GetCell(4).ToString() != "")
                    {
                        model.InstituNum_town = int.Parse(row.GetCell(4).ToString());
                    }
                    else
                    {
                        model.InstituNum_town = null;
                    }
                    if (row.GetCell(5) != null && row.GetCell(5).ToString() != "")
                    {
                        model.PeopleNum = int.Parse(row.GetCell(5).ToString());
                    }
                    else
                    {
                        model.PeopleNum = null;
                    }
                    if (row.GetCell(6) != null && row.GetCell(6).ToString() != "")
                    {
                        model.PeopleNum_City = int.Parse(row.GetCell(6).ToString());
                    }
                    else
                    {
                        model.PeopleNum_City = null;
                    }
                    if (row.GetCell(7) != null && row.GetCell(7).ToString() != "")
                    {
                        model.PeopleNum_County = int.Parse(row.GetCell(7).ToString());
                    }
                    else
                    {
                        model.PeopleNum_County = null;
                    }
                    if (row.GetCell(8) != null && row.GetCell(8).ToString() != "")
                    {
                        model.PeopleNum_man = int.Parse(row.GetCell(8).ToString());
                    }
                    else
                    {
                        model.PeopleNum_man = null;
                    }
                    if (row.GetCell(9) != null && row.GetCell(9).ToString() != "")
                    {
                        model.PeopleNum_woman = int.Parse(row.GetCell(9).ToString());
                    }
                    else
                    {
                        model.PeopleNum_woman = null;
                    }
                    if (row.GetCell(10) != null && row.GetCell(10).ToString() != "")
                    {
                        model.PeopleNum_age1 = int.Parse(row.GetCell(10).ToString());
                    }
                    else
                    {
                        model.PeopleNum_age1 = null;
                    }
                    if (row.GetCell(11) != null && row.GetCell(11).ToString() != "")
                    {
                        model.PeopleNum_age2 = int.Parse(row.GetCell(11).ToString());
                    }
                    else
                    {
                        model.PeopleNum_age2 = null;
                    }
                    if (row.GetCell(12) != null && row.GetCell(12).ToString() != "")
                    {
                        model.PeopleNum_age3 = int.Parse(row.GetCell(12).ToString());
                    }
                    else
                    {
                        model.PeopleNum_age3 = null;
                    }
                    if (row.GetCell(13) != null && row.GetCell(13).ToString() != "")
                    {
                        model.PeopleNum_age4 = int.Parse(row.GetCell(13).ToString());
                    }
                    else
                    {
                        model.PeopleNum_age4 = null;
                    }
                    if (row.GetCell(14) != null && row.GetCell(14).ToString() != "")
                    {
                        model.PeopleNum_xlss = int.Parse(row.GetCell(14).ToString());
                    }
                    else
                    {
                        model.PeopleNum_xlss = null;
                    }
                    if (row.GetCell(15) != null && row.GetCell(15).ToString() != "")
                    {
                        model.PeopleNum_xlbk = int.Parse(row.GetCell(15).ToString());
                    }
                    else
                    {
                        model.PeopleNum_xlbk = null;
                    }
                    if (row.GetCell(16) != null && row.GetCell(16).ToString() != "")
                    {
                        model.PeopleNum_xldz = int.Parse(row.GetCell(16).ToString());
                    }
                    else
                    {
                        model.PeopleNum_xldz = null;
                    }
                    if (row.GetCell(17) != null && row.GetCell(17).ToString() != "")
                    {
                        model.PeopleNum_xlzz = int.Parse(row.GetCell(17).ToString());
                    }
                    else
                    {
                        model.PeopleNum_xlzz = null;
                    }
                    if (row.GetCell(18) != null && row.GetCell(18).ToString() != "")
                    {
                        model.PeopleNum_xlqt = int.Parse(row.GetCell(18).ToString());
                    }
                    else
                    {
                        model.PeopleNum_xlqt = null;
                    }
                    if (row.GetCell(19) != null && row.GetCell(19).ToString() != "")
                    {
                        model.PeopleNum_zczgj = int.Parse(row.GetCell(19).ToString());
                    }
                    else
                    {
                        model.PeopleNum_zczgj = null;
                    }
                    if (row.GetCell(20) != null && row.GetCell(20).ToString() != "")
                    {
                        model.PeopleNum_zcfgj = int.Parse(row.GetCell(20).ToString());
                    }
                    else
                    {
                        model.PeopleNum_zcfgj = null;
                    }
                    if (row.GetCell(21) != null && row.GetCell(21).ToString() != "")
                    {
                        model.PeopleNum_zczj = int.Parse(row.GetCell(21).ToString());
                    }
                    else
                    {
                        model.PeopleNum_zczj = null;
                    }
                    if (row.GetCell(22) != null && row.GetCell(22).ToString() != "")
                    {
                        model.PeopleNum_zccj = int.Parse(row.GetCell(22).ToString());
                    }
                    else
                    {
                        model.PeopleNum_zccj = null;
                    }
                    if (row.GetCell(23) != null && row.GetCell(23).ToString() != "")
                    {
                        model.PeopleNum_zcqt = int.Parse(row.GetCell(23).ToString());
                    }
                    else
                    {
                        model.PeopleNum_zcqt = null;
                    }
                    list.Add(model);
                }
            }
            return list;
        }
        /// <summary>
        /// 判定是否生成过报表
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns>没生成过true,否则false</returns>
        public bool CheckRepCount(string reportId)
        {
            string strSql = @"select count(1) from dbo.T_FruTecPromotion where ReportId=@ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportId = reportId }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("更新表3生产情况调查表T_FruTecPromotion对象信息失败", ex);
                return false;
            }
        }
        /// <summary>
        /// 初始化报表内容
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public ResResult<int> AddInfo(FruTecPromotionModel list)
        {
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"INSERT INTO dbo.T_FruTecPromotion";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "SortCode,";
            strSql += "SortName,";
            strSql += "SortSonCode,";
            strSql += "SortSonName,";
            strSql += "ItemCode,";
            strSql += "ItemName,";
            strSql += "Quantity,";
            strSql += "Unit)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@SortCode,";
            strSql += "@SortName,";
            strSql += "@SortSonCode,";
            strSql += "@SortSonName,";
            strSql += "@ItemCode,";
            strSql += "@ItemName,";
            strSql += "@Quantity,";
            strSql += "@Unit)";
            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                try
                {
                    int result = dbCon.Execute(strSql, list);
                    dbCon.Dispose();//数据库连接释放
                    obj.message = "数据添加成功";
                    obj.status= DefaultKey.SuccessResult;
                }
                catch (Exception ex)
                {
                    LogHelper.ErrorLog("新增表3生产情况调查表T_FruTecPromotion对象信息失败", ex);
                    dbCon.Dispose();//数据库连接释放
                    obj.message = "新增生产情况调查表T_FruTecPromotion对象信息失败";
                    obj.status=DefaultKey.ErrorResult;

                }
            }
            return obj;
        }
        public ResResult<int> UpdateInfo(FruTecPromotionModel list)
        {
            list.UpdateTime = DateTime.Now;
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"UPDATE dbo.T_FruTecPromotion set ";
            strSql += "ReportId = @ReportId,";
            strSql += "InstituNum = @InstituNum,";
            strSql += "InstituNum_City = @InstituNum_City,";
            strSql += "InstituNum_Area = @InstituNum_Area,";
            strSql += "InstituNum_town = @InstituNum_town,";
            strSql += "PeopleNum = @PeopleNum,";
            strSql += "PeopleNum_City = @PeopleNum_City,";
            strSql += "PeopleNum_County = @PeopleNum_County,";
            strSql += "PeopleNum_man = @PeopleNum_man,";
            strSql += "PeopleNum_woman = @PeopleNum_woman,";
            strSql += "PeopleNum_age1 = @PeopleNum_age1,";
            strSql += "PeopleNum_age2 = @PeopleNum_age2,";
            strSql += "PeopleNum_age3 = @PeopleNum_age3,";
            strSql += "PeopleNum_age4 = @PeopleNum_age4,";
            strSql += "PeopleNum_xlss = @PeopleNum_xlss,";
            strSql += "PeopleNum_xlbk = @PeopleNum_xlbk,";
            strSql += "PeopleNum_xldz = @PeopleNum_xldz,";
            strSql += "PeopleNum_xlzz = @PeopleNum_xlzz,";
            strSql += "PeopleNum_xlqt = @PeopleNum_xlqt,";
            strSql += "PeopleNum_zczgj = @PeopleNum_zczgj,";
            strSql += "PeopleNum_zcfgj = @PeopleNum_zcfgj,";
            strSql += "PeopleNum_zczj = @PeopleNum_zczj,";
            strSql += "PeopleNum_zccj = @PeopleNum_zccj,";
            strSql += "PeopleNum_zcqt = @PeopleNum_zcqt,";
            strSql += "AddUser = @AddUser,";
            strSql += "AddTime = @AddTime,";
            strSql += "UpdateUser = @UpdateUser,";
            strSql += "UpdateTime = @UpdateTime";
            strSql += " where ReportId = @ReportId"; ;
            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                var trans = dbCon.BeginTransaction();
                try
                {
                    if (list != null)
                    {                        
                        string strRep = "update dbo.T_Report set Status='2',UpdateTime=@UpdateTime where Id=@ReportId";
                        dbCon.Execute(strRep, new { ReportId = list.ReportId, UpdateTime = DateTime.Now }, trans);
                    }
                    int result = dbCon.Execute(strSql, list, trans);
                    //提交事务
                    trans.Commit();
                    obj.model = result;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "更新成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;

                }
                catch (Exception ex)
                {
                    //出现异常，事务Rollback
                    trans.Rollback();
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("更新表3生产情况调查表T_FruTecPromotion对象信息失败", ex);
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
        }
        public  ResResult<FruTecPromotionEntity> GetModel(string ReportId)
        {
            ResResult<FruTecPromotionEntity> obj = new ResResult<FruTecPromotionEntity>();
            FruTecPromotionEntity model = new FruTecPromotionEntity();
            if (CheckRepCount(ReportId))
            {
                model.ReportId = ReportId;
                if (AddInfo(model).status > 0)
                {
                    obj.model = model;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                }
            }
            else
            {
                string strSql = @"select * from dbo.T_FruTecPromotion where ReportId = @ReportId";
                try
                {
                    using (var dbCon = ConnectFactory.GetOpenConnection())
                    {
                        obj.model = dbCon.Query<FruTecPromotionEntity>(strSql, new { ReportId = ReportId }).FirstOrDefault();
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "获取成功！";
                        dbCon.Dispose();//数据库连接释放
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("获取水果技术推广机构与人员情况调查T_FruTecPromotion对象信息失败", ex);
                }
            }
            return obj;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        /// <returns></returns>
        public ResResult<List<PromotionStatisModel>> getStatisListData(string AreaCode, string FillInYear)
        {
            ResResult<List<PromotionStatisModel>> obj = new ResResult<List<PromotionStatisModel>>();
            List<PromotionStatisModel> list = new List<PromotionStatisModel>();
            AreaServices areaServices = new AreaServices();
            if (AreaCode.Equals("530000000000"))
            {
                //var citylist = areaServices.GetPrefectureList();
                //PromotionStatisModel totalModel = new PromotionStatisModel();
                //totalModel.AreaCode = AreaCode;
                //totalModel.AreaName = "合计";
                //totalModel.tag = 2;
                //totalModel.InstituNum = 0;
                //totalModel.InstituNum_City = 0;
                //totalModel.InstituNum_Area = 0;
                //totalModel.InstituNum_town = 0;
                //totalModel.PeopleNum = 0;
                //totalModel.PeopleNum_City = 0;
                //totalModel.PeopleNum_County = 0;
                //totalModel.PeopleNum_man = 0;
                //totalModel.PeopleNum_woman = 0;
                //totalModel.PeopleNum_age1 = 0;
                //totalModel.PeopleNum_age2 = 0;
                //totalModel.PeopleNum_age3 = 0;
                //totalModel.PeopleNum_age4 = 0;
                //totalModel.PeopleNum_xlss = 0;
                //totalModel.PeopleNum_xlbk = 0;
                //totalModel.PeopleNum_xldz = 0;
                //totalModel.PeopleNum_xlzz = 0;
                //totalModel.PeopleNum_xlqt = 0;
                //totalModel.PeopleNum_zczgj = 0;
                //totalModel.PeopleNum_zcfgj = 0;
                //totalModel.PeopleNum_zczj = 0;
                //totalModel.PeopleNum_zccj = 0;
                //totalModel.PeopleNum_zcqt = 0;                
                //foreach (string code in citylist)
                //{
                //    PromotionStatisModel model = getProvinStatisList(code, FillInYear);
                //    if (model != null)
                //    {
                //        model.AreaName = areaServices.GetByCodeModel(code).model.FullName;
                //        model.AreaCode = code;
                //        totalModel.InstituNum +=model.InstituNum;
                //        totalModel.InstituNum_City +=model.InstituNum_City;
                //        totalModel.InstituNum_Area +=model.InstituNum_Area;
                //        totalModel.InstituNum_town +=model.InstituNum_town;
                //        totalModel.PeopleNum +=model.PeopleNum;
                //        totalModel.PeopleNum_City +=model.PeopleNum_City;
                //        totalModel.PeopleNum_County +=model.PeopleNum_County;
                //        totalModel.PeopleNum_man +=model.PeopleNum_man;
                //        totalModel.PeopleNum_woman +=model.PeopleNum_woman;
                //        totalModel.PeopleNum_age1 +=model.PeopleNum_age1;
                //        totalModel.PeopleNum_age2 +=model.PeopleNum_age2;
                //        totalModel.PeopleNum_age3 +=model.PeopleNum_age3;
                //        totalModel.PeopleNum_age4 +=model.PeopleNum_age4;
                //        totalModel.PeopleNum_xlss +=model.PeopleNum_xlss;
                //        totalModel.PeopleNum_xlbk +=model.PeopleNum_xlbk;
                //        totalModel.PeopleNum_xldz +=model.PeopleNum_xldz;
                //        totalModel.PeopleNum_xlzz +=model.PeopleNum_xlzz;
                //        totalModel.PeopleNum_xlqt +=model.PeopleNum_xlqt;
                //        totalModel.PeopleNum_zczgj +=model.PeopleNum_zczgj;
                //        totalModel.PeopleNum_zcfgj +=model.PeopleNum_zcfgj;
                //        totalModel.PeopleNum_zczj +=model.PeopleNum_zczj;
                //        totalModel.PeopleNum_zccj +=model.PeopleNum_zccj;
                //        totalModel.PeopleNum_zcqt +=model.PeopleNum_zcqt;                      
                //        list.Add(model);
                //    }
                //}
                //list.Add(totalModel);
                list = getProvinStatisList(FillInYear);
            }
            else
            {
                if (AreaCode.Substring(4, 8).Equals("00000000"))
                {
                    list = getStatisList(AreaCode, FillInYear);
                    int len = list.Count;
                    if(len != 0)
                    {
                        list[len - 1].AreaName = "合计";
                    }
                  //  list[len - 1].tag = 1;
                }
                else
                {
                    list = getCountyStatisList(AreaCode, FillInYear);
                }
            }
            obj.model = list;
            obj.status = DefaultKey.SuccessResult;
            obj.message = "获取成功";
            return obj;
        }
        /// <summary>
        /// 省级汇总
        /// </summary>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        /// <returns></returns>
        //public PromotionStatisModel getProvinStatisList(string AreaCode, string FillInYear)
        //{
        //    PromotionStatisModel list = new PromotionStatisModel();
        //    string strSql = "select ";            
        //    strSql += "isnull(sum(t3.InstituNum),0.00) InstituNum ,";
        //    strSql += "isnull(sum(t3.InstituNum_City),0.00) InstituNum_City ,";
        //    strSql += "isnull(sum(t3.InstituNum_Area),0.00) InstituNum_Area ,";
        //    strSql += "isnull(sum(t3.InstituNum_town),0.00) InstituNum_town ,";
        //    strSql += "isnull(sum(t3.PeopleNum),0.00) PeopleNum,";
        //    strSql += "isnull(sum(t3.PeopleNum_City),0.00) PeopleNum_City,";
        //    strSql += "isnull(sum(t3.PeopleNum_County),0.00) PeopleNum_County,";
        //    strSql += "isnull(sum(t3.PeopleNum_man),0.00) PeopleNum_man ,";
        //    strSql += "isnull(sum(t3.PeopleNum_woman),0.00) PeopleNum_woman ,";
        //    strSql += "isnull(sum(t3.PeopleNum_age1),0.00) PeopleNum_age1 ,";
        //    strSql += "isnull(sum(t3.PeopleNum_age2),0.00) PeopleNum_age2 ,";
        //    strSql += "isnull(sum(t3.PeopleNum_age3),0.00) PeopleNum_age3 ,";
        //    strSql += "isnull(sum(t3.PeopleNum_age4),0.00) PeopleNum_age4 ,";
        //    strSql += "isnull(sum(t3.PeopleNum_xlss),0.00) PeopleNum_xlss ,";
        //    strSql += "isnull(sum(t3.PeopleNum_xlbk),0.00) PeopleNum_xlbk ,";
        //    strSql += "isnull(sum(t3.PeopleNum_xldz),0.00) PeopleNum_xldz ,";
        //    strSql += "isnull(sum(t3.PeopleNum_xlzz),0.00) PeopleNum_xlzz ,";
        //    strSql += "isnull(sum(t3.PeopleNum_xlqt),0.00) PeopleNum_xlqt ,";
        //    strSql += "isnull(sum(t3.PeopleNum_zczgj),0.00) PeopleNum_zczgj ,";
        //    strSql += "isnull(sum(t3.PeopleNum_zcfgj),0.00) PeopleNum_zcfgj ,";
        //    strSql += "isnull(sum(t3.PeopleNum_zczj),0.00) PeopleNum_zczj ,";
        //    strSql += "isnull(sum(t3.PeopleNum_zccj),0.00) PeopleNum_zccj ,";
        //    strSql += "isnull(sum(t3.PeopleNum_zcqt),0.00) PeopleNum_zcqt";
        //    strSql += " from T_Area t1 left join ";
        //    strSql += " T_Report t2 on t2.AreaCode = t1.Code";
        //    strSql += " left join  T_FruTecPromotion t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruTecPromotion'  and t2.FillInYear = @FillInYear ";
        //    strSql += " and (t1.ParentCode = @AreaCode or t1.Code = @AreaCode) ";//加入州级人员表的数据
        //    strSql += "  and t2.Status = '4' ";
        //    try
        //    {
        //        using (var dbCon = ConnectFactory.GetOpenConnection())
        //        {
        //            list = dbCon.Query<PromotionStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault();
        //            dbCon.Dispose();//数据库连接释放
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        return null;
        //        LogHelper.ErrorLog("获取水果经营实体情况调查统计列表信息失败", ex);
        //    }
        //    return list;
        //}
        public List<PromotionStatisModel> getProvinStatisList(string FillInYear)
        {
            List<PromotionStatisModel> list = new List<PromotionStatisModel>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ta.FullName AreaName,tt.* from (");
            strSql.Append("select case when grouping(t1.ParentCode)=1 then '530000000000' else ParentCode end AreaCode,");
            strSql.Append("isnull(sum(t3.InstituNum),0.00) InstituNum ,");
            strSql.Append("isnull(sum(t3.InstituNum_City),0.00) InstituNum_City ,");
            strSql.Append("isnull(sum(t3.InstituNum_Area),0.00) InstituNum_Area ,");
            strSql.Append("isnull(sum(t3.InstituNum_town),0.00) InstituNum_town ,");
            strSql.Append("isnull(sum(t3.PeopleNum),0.00) PeopleNum,");
            strSql.Append("isnull(sum(t3.PeopleNum_City),0.00) PeopleNum_City,");
            strSql.Append("isnull(sum(t3.PeopleNum_County),0.00) PeopleNum_County,");
            strSql.Append("isnull(sum(t3.PeopleNum_man),0.00) PeopleNum_man ,");
            strSql.Append("isnull(sum(t3.PeopleNum_woman),0.00) PeopleNum_woman ,");
            strSql.Append("isnull(sum(t3.PeopleNum_age1),0.00) PeopleNum_age1 ,");
            strSql.Append("isnull(sum(t3.PeopleNum_age2),0.00) PeopleNum_age2 ,");
            strSql.Append("isnull(sum(t3.PeopleNum_age3),0.00) PeopleNum_age3 ,");
            strSql.Append("isnull(sum(t3.PeopleNum_age4),0.00) PeopleNum_age4 , ");
            strSql.Append("isnull(sum(t3.PeopleNum_xlss),0.00) PeopleNum_xlss ,");
            strSql.Append("isnull(sum(t3.PeopleNum_xlbk),0.00) PeopleNum_xlbk ,");
            strSql.Append("isnull(sum(t3.PeopleNum_xldz),0.00) PeopleNum_xldz ,");
            strSql.Append("isnull(sum(t3.PeopleNum_xlzz),0.00) PeopleNum_xlzz ,");
            strSql.Append("isnull(sum(t3.PeopleNum_xlqt),0.00) PeopleNum_xlqt ,");
            strSql.Append("isnull(sum(t3.PeopleNum_zczgj),0.00) PeopleNum_zczgj ,");
            strSql.Append("isnull(sum(t3.PeopleNum_zcfgj),0.00) PeopleNum_zcfgj ,");
            strSql.Append("isnull(sum(t3.PeopleNum_zczj),0.00) PeopleNum_zczj ,");
            strSql.Append("isnull(sum(t3.PeopleNum_zccj),0.00) PeopleNum_zccj ,");
            strSql.Append("isnull(sum(t3.PeopleNum_zcqt),0.00) PeopleNum_zcqt  ");
            strSql.Append("from T_Area t1 left join ");
            strSql.Append(" T_Report t2 on t2.AreaCode = t1.Code ");
            strSql.Append("left join  T_FruTecPromotion t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruTecPromotion' and t2.FillInYear = @FillInYear and t1.ParentCode in ( ");
            strSql.Append(" select Code from T_Area where ParentCode = '530000000000' )");
            strSql.Append("  and t2.Status = '4' ");
            strSql.Append(" group by rollup(t1.ParentCode) )tt");
            strSql.Append(" inner join T_Area ta on tt.AreaCode = ta.Code");
            strSql.Append(" order by  (case Code when '530000000000' Then 1 Else 0 End),Code ");
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<PromotionStatisModel>(strSql.ToString(), new { FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取水果经营实体情况调查统计列表信息失败", ex);
            }
            return list;
        }
        /// <summary>
        /// 州市汇总
        /// </summary>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        /// <returns></returns>
        public List<PromotionStatisModel> getStatisList(string AreaCode, string FillInYear)
        {
            List<PromotionStatisModel> list = new List<PromotionStatisModel>();
            string strSql = "select t.FullName AreaName, AreaCode,tt.* from (";
            strSql += "select case when grouping(t1.Code)=1 then '" + "999" + "' else Code end AreaCode,";
            strSql += "isnull(sum(t3.InstituNum),0.00) InstituNum ,";
            strSql += "isnull(sum(t3.InstituNum_City),0.00) InstituNum_City ,";
            strSql += "isnull(sum(t3.InstituNum_Area),0.00) InstituNum_Area ,";
            strSql += "isnull(sum(t3.InstituNum_town),0.00) InstituNum_town ,";
            strSql += "isnull(sum(t3.PeopleNum),0.00) PeopleNum,";
            strSql += "isnull(sum(t3.PeopleNum_City),0.00) PeopleNum_City,";
            strSql += "isnull(sum(t3.PeopleNum_County),0.00) PeopleNum_County,";
            strSql += "isnull(sum(t3.PeopleNum_man),0.00) PeopleNum_man ,";
            strSql += "isnull(sum(t3.PeopleNum_woman),0.00) PeopleNum_woman ,";
            strSql += "isnull(sum(t3.PeopleNum_age1),0.00) PeopleNum_age1 ,";
            strSql += "isnull(sum(t3.PeopleNum_age2),0.00) PeopleNum_age2 ,";
            strSql += "isnull(sum(t3.PeopleNum_age3),0.00) PeopleNum_age3 ,";
            strSql += "isnull(sum(t3.PeopleNum_age4),0.00) PeopleNum_age4 ,";
            strSql += "isnull(sum(t3.PeopleNum_xlss),0.00) PeopleNum_xlss ,";
            strSql += "isnull(sum(t3.PeopleNum_xlbk),0.00) PeopleNum_xlbk ,";
            strSql += "isnull(sum(t3.PeopleNum_xldz),0.00) PeopleNum_xldz ,";
            strSql += "isnull(sum(t3.PeopleNum_xlzz),0.00) PeopleNum_xlzz ,";
            strSql += "isnull(sum(t3.PeopleNum_xlqt),0.00) PeopleNum_xlqt ,";
            strSql += "isnull(sum(t3.PeopleNum_zczgj),0.00) PeopleNum_zczgj ,";
            strSql += "isnull(sum(t3.PeopleNum_zcfgj),0.00) PeopleNum_zcfgj ,";
            strSql += "isnull(sum(t3.PeopleNum_zczj),0.00) PeopleNum_zczj ,";
            strSql += "isnull(sum(t3.PeopleNum_zccj),0.00) PeopleNum_zccj ,";
            strSql += "isnull(sum(t3.PeopleNum_zcqt),0.00) PeopleNum_zcqt";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code";
            strSql += " left join  T_FruTecPromotion t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruTecPromotion'  and t2.FillInYear = @FillInYear ";
            strSql += "  and (t1.ParentCode = @AreaCode or  t1.Code = @AreaCode) ";//加入州级人员表的数据
            strSql += "  and t2.Status = '4' ";
            strSql += " group by rollup(t1.Code)";
            strSql += " )tt left join  T_Area t on t.Code = tt.AreaCode order by t.Code desc";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<PromotionStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取水果经营实体情况调查统计列表信息失败", ex);
            }
            return list;
        }
        /// <summary>
        /// 区县汇总
        /// </summary>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        /// <returns></returns>
        public List<PromotionStatisModel> getCountyStatisList(string AreaCode, string FillInYear)
        {
            List<PromotionStatisModel> list = new List<PromotionStatisModel>();
            string strSql = "select t1.FullName AreaName,t.Code AreaCode,";
            strSql += "isnull(t3.InstituNum,0.00) InstituNum ,";
            strSql += "isnull(t3.InstituNum_City,0.00) InstituNum_City ,";
            strSql += "isnull(t3.InstituNum_Area,0.00) InstituNum_Area ,";
            strSql += "isnull(t3.InstituNum_town,0.00) InstituNum_town ,";
            strSql += "isnull(t3.PeopleNum,0.00) PeopleNum,";
            strSql += "isnull(t3.PeopleNum_City,0.00) PeopleNum_City,";
            strSql += "isnull(t3.PeopleNum_County,0.00) PeopleNum_County,";
            strSql += "isnull(t3.PeopleNum_man,0.00) PeopleNum_man ,";
            strSql += "isnull(t3.PeopleNum_woman,0.00) PeopleNum_woman ,";
            strSql += "isnull(t3.PeopleNum_age1,0.00) PeopleNum_age1 ,";
            strSql += "isnull(t3.PeopleNum_age2,0.00) PeopleNum_age2 ,";
            strSql += "isnull(t3.PeopleNum_age3,0.00) PeopleNum_age3 ,";
            strSql += "isnull(t3.PeopleNum_age4,0.00) PeopleNum_age4 ,";
            strSql += "isnull(t3.PeopleNum_xlss,0.00) PeopleNum_xlss ,";
            strSql += "isnull(t3.PeopleNum_xlbk,0.00) PeopleNum_xlbk ,";
            strSql += "isnull(t3.PeopleNum_xldz,0.00) PeopleNum_xldz ,";
            strSql += "isnull(t3.PeopleNum_xlzz,0.00) PeopleNum_xlzz ,";
            strSql += "isnull(t3.PeopleNum_xlqt,0.00) PeopleNum_xlqt ,";
            strSql += "isnull(t3.PeopleNum_zczgj,0.00) PeopleNum_zczgj ,";
            strSql += "isnull(t3.PeopleNum_zcfgj,0.00) PeopleNum_zcfgj ,";
            strSql += "isnull(t3.PeopleNum_zczj,0.00) PeopleNum_zczj ,";
            strSql += "isnull(t3.PeopleNum_zccj,0.00) PeopleNum_zccj ,";
            strSql += "isnull(t3.PeopleNum_zcqt,0.00) PeopleNum_zcqt";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code";
            strSql += " left join  T_FruTecPromotion t3 on t2.Id = t3.ReportId where t2.ReportCode = 'FruTecPromotion' and t1.Code = @AreaCode and t2.FillInYear = @FillInYear";
            strSql += "  and t2.Status = '4' ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<PromotionStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取水果经营实体情况调查统计列表信息失败", ex);
            }
            return list;
        }

    }
}